import basedata 
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.utils import get_column_letter
from openpyxl.styles import fills,colors,NamedStyle,Font,Side,Border,PatternFill,Alignment,Protection
import numpy as np 
import pathlib
import os
import sqlite3
import decimal
import mpmath
import time
# convert
root=pathlib.Path(__file__).parent

color_scale_rule = ColorScaleRule(
    start_type="min", start_color="FF0000",    # 红色（起点）
    mid_type="percentile", mid_value=50,      # 50%分位（中点）
    mid_color="FFFF00",                       # 黄色（中点色）
    end_type="max", end_color="00CD00"        # 绿色（终点）
)

# db=sqlite3.connect('file:cachedb?mode=memory&cache=shared', uri=True)
db=sqlite3.connect(':memory:')
# db=sqlite3.connect(str(root/"cj1.db"))
cur = db.cursor()
tit=["考生号","姓名","班级","语文","数学","英语","物理","化学","生物","道法","历史","地理","总分"]
RecordNumber={}
zhda=[]
listt=None
def inputdate():
    dbt=sqlite3.connect(str(root/"cj.db"))
    curt = dbt.cursor()
    r=curt.execute("select `考生号`,`姓名`,`班级`,`语文`,`数学`,`英语`,`物理`,`化学`,`生物`,`道法`,`历史`,`地理`,`总分` from main")
    da=r.fetchall()
    

    for i in da:
        t=[]
        print(i)
        for n in tit:
            
            ins=i[tit.index(n)]
            if(n in ("考生号","班级")):
                t.append(str(ins))
            elif(n=="姓名"):
                t.append(str(ins))
            elif(ins==None or ins=="None"):
                t.append(None)
            elif(n=="总分"):
                zf=decimal.Decimal('0')
                for i2 in tit:
                    if ((i2 in ("语文","数学","英语","物理","化学","生物","道法","历史","地理")) and (i[tit.index(i2)] != None) and (i[tit.index(i2)] != 'None')):
                        zf=zf+mpmath.mpf(basedata.zh(i2,i[tit.index(i2)]))
                t.append(float(zf))
            else:
                t.append(float(basedata.zh(n,ins)))
        zhda.append(t)
    r=None
    da=None
    dbt=None
    curt=None

    # print(zhda)


    try:
        sql='''
            CREATE TABLE "main" (
            "考生号" INTEGER NOT NULL,
            "姓名" TEXT,
            "班级" TEXT,
            "语文" real,
            "数学" real,
            "英语" real,
            "物理" real,
            "化学" real,
            "生物" real,
            "道法" real,
            "历史" real,
            "地理" real,
            "总分" real,
            PRIMARY KEY ("考生号")
            );

        '''
        cur.execute(sql)
        db.commit()
    except:
        print("main已经存在")
        try:
            sql="DELETE FROM main"
            cur.execute(sql)
            db.commit()
        except:
            print("表清空失败")
            exit()
        else:
            print("表已经清空")
    else :
        print("main已经创建")

    cur.executemany("REPLACE INTO main(`考生号`,`姓名`,`班级`,`语文`,`数学`,`英语`,`物理`,`化学`,`生物`,`道法`,`历史`,`地理`,`总分`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?);",zhda)
    db.commit()

class summary():
    def __init__(self):
        self.grade=None
        self.subject=None
        self.classGroup=None
        self.wb=Workbook()
        self.ws=self.wb.active
        self.row=1
        self.col=1
        # if(basedata.ScoreConversion):
        #     title="折合平均分汇总"
        # else:
        #     title="原始平均分汇总"
        # self.ws.cell(self.row,self.col,basedata.titleH+title).font=basedata.fontTitle
        # self.saveWb(str(root/f"output/{title}.xlsx"))
    def _get_cell_address(self,row,col):
        col_=get_column_letter(col)
        return f"{col_}{row}"
    def mainLoop(self):
        self.row=2
        self.col=1
        for i in basedata.km:
            self.ws.cell(self.row,1,i).font=basedata.fontTitle
            self.ws.cell(self.row,1).alignment=basedata.alignment
            n=0
            self.row=self.row +1
            self.col=3
            self.ws.cell(self.row,2,"班级").alignment=basedata.alignment
            self.ws.cell(self.row,1).border=basedata.bd
            self.ws.cell(self.row,2).border=basedata.bd
            for x in basedata.group[i]:
                for x1 in x:
                    if(basedata.alias.__contains__(str(x1))):
                        self.ws.cell(self.row,self.col,basedata.alias[str(x1)]+"班").alignment=basedata.alignment
                    else:
                        self.ws.cell(self.row,self.col,str(x1)+"班").alignment=basedata.alignment
                    self.ws.cell(self.row,self.col).border=basedata.bd
                    self.col=self.col+1
                n=n+len(x)
            self.ws.merge_cells(start_row=self.row-1, end_row=self.row-1, start_column=1, end_column=n+2)#合并
            print(self._get_cell_address(self.row,self.col))

            self.ws.conditional_formatting.add("E4:J4",color_scale_rule)
            self.row=self.row+1
            
            listt=basedata.km[i]+["总分"]
            # listt.append("总分")
            for n in listt:
                self.grade=i
                self.subject=n
               
                self.ws.cell(self.row,1,self.subject).border=basedata.bd
                self.ws.cell(self.row,1).alignment=basedata.alignment
                if(basedata.cppjttj):
                    self.ws.merge_cells(start_row=self.row, end_row=self.row+7, start_column=1, end_column=1)#合并
                else:
                    self.ws.merge_cells(start_row=self.row, end_row=self.row+3, start_column=1, end_column=1)#合并
                self.ws.cell(self.row,2,"班级平均分").border=basedata.bd
                self.ws.cell(self.row+1,2,"名次").border=basedata.bd
                self.ws.cell(self.row+2,2,"年级平均分").border=basedata.bd
                self.ws.cell(self.row+3,2,"差值").border=basedata.bd
                if(basedata.cppjttj):
                    self.ws.cell(self.row+4,2,"评比班级平均分").border=basedata.bd
                    self.ws.cell(self.row+5,2,"名次").border=basedata.bd
                    self.ws.cell(self.row+6,2,"评比年级平均分").border=basedata.bd
                    self.ws.cell(self.row+7,2,"差值").border=basedata.bd
                    self.gradeLoop()
                    self.row=self.row+8
                else:
                    self.gradeLoop()
                    self.row=self.row+4
        qkt="不参与统计" if basedata.MissingExamination == None else "按0分统计"
        self.ws.cell(self.row+1,1,"生成时间：{}，缺考考生{}".format(time.strftime("%Y年%m月%d日",time.localtime()),qkt))
                
                # self.saveWb(str(root/"output/汇总.xlsx"))

    def gradeLoop(self):
        self.col=3
        for g in basedata.group[self.grade]:
            
            self.classGroup=g
            datas=self.getData()
            da=[]
            groupSize=len(datas)
            n=0
            cprsn={}
            cppjf={} #参评平均分
            sjpjf={}  #实际平均分
            for i in datas:
                cprsn[str(i)]=max(basedata.getTotal(i,datas[str(i)],self.subject),RecordNumber[str(i)])
                cppjf[str(i)]=np.nansum(datas[str(i)])/cprsn[str(i)]
                sjpjf[str(i)]=np.nanmean(datas[str(i)])
                da=da+datas[str(i)] #全部分数

            njpjf=np.nanmean(list(cppjf.values()))  #年级参评平均分
            njsjpjf=np.nanmean(list(sjpjf.values())) #年级实际平均分
            #njpjf=np.sum(datas[i])/cprsn[i]
            mc=[""] #实考名次
            for i in sorted(sjpjf.items(), key = lambda kv:kv[1],reverse=True):
                mc.append(i[0])
            cpmc=[""] #参评名次
            for i in sorted(cppjf.items(), key = lambda kv:kv[1],reverse=True):
                cpmc.append(i[0])
            for i in datas:
                i=str(i)
                self.ws.cell(self.row,self.col+n,float(f"{sjpjf[i]:.{basedata.DecimalPoint}f}")) #实际平均分
                self.ws.cell(self.row+1,self.col+n,float(f"{mc.index(i):d}")) #实际平均分
                self.ws.cell(self.row+3,self.col+n,float(f"{(sjpjf[i]-njsjpjf):+.{basedata.DecimalPoint}f}"))
                self.ws.cell(self.row,self.col+n).border=basedata.bd
                self.ws.cell(self.row+1,self.col+n).border=basedata.bd
                self.ws.cell(self.row+3,self.col+n).border=basedata.bd
                if(basedata.cppjttj):
                    self.ws.cell(self.row+4,self.col+n,float(f"{cppjf[i]:.{basedata.DecimalPoint}f}"))  #参评平均分
                    self.ws.cell(self.row+5,self.col+n,float(f"{cpmc.index(i):d}")) #实际平均分
                    self.ws.cell(self.row+7,self.col+n,float(f"{(cppjf[i]-njpjf):+.{basedata.DecimalPoint}f}"))
                    self.ws.cell(self.row+4,self.col+n).border=basedata.bd
                    self.ws.cell(self.row+5,self.col+n).border=basedata.bd
                    self.ws.cell(self.row+7,self.col+n).border=basedata.bd
                n=n+1
                print(self.subject,i,np.nanmean(datas[i]))
            self.ws.cell(self.row+2,self.col,float(f"{njsjpjf:.{basedata.DecimalPoint}f}"))
            self.ws.cell(self.row+2,self.col).border=basedata.bd
            self.ws.cell(self.row+2,self.col).border=basedata.bd
            self.ws.cell(self.row+2,self.col).alignment=basedata.alignment
            if(basedata.cppjttj):
                self.ws.cell(self.row+6,self.col,float(f"{njpjf:.{basedata.DecimalPoint}f}"))
                self.ws.cell(self.row+6,self.col).border=basedata.bd
                self.ws.cell(self.row+6,self.col).alignment=basedata.alignment
            if(groupSize>1):
                address_start=self._get_cell_address(self.row,self.col)
                address_end=self._get_cell_address(self.row,self.col + groupSize-1)
                self.ws.conditional_formatting.add(f"{address_start}:{address_end}",color_scale_rule) #设置平均分的条件格式->色阶
                self.ws.merge_cells(start_row=self.row+2, end_row=self.row+2, start_column=self.col, end_column =self.col + groupSize-1) #合并年级平均分单元格
                if(basedata.cppjttj):
                    self.ws.merge_cells(start_row=self.row+6, end_row=self.row+6, start_column=self.col, end_column =self.col + groupSize-1) #合并年级平均分单元格
            
            self.col=self.col+groupSize
        



    def getData(self):
        datas={}
        #datas["all"]=[]
        for i in self.classGroup:
            datas[str(i)]=[]
            sql="select `{}` from main where `班级`= '{}'".format(self.subject,str(i))
            r=cur.execute(sql)
            list = r.fetchall()
            if(basedata.MissingExamination != None):
                RecordNumber[str(i)]=len(list)
            else:
                RecordNumber[str(i)]=0
            for n in list:
                if (n[0]=="" or n[0]=="Null" or n[0]==None ):
                    if(basedata.MissingExamination == None):
                        continue
                    else:
                        datas[str(i)].append(0)
                else:
                    datas[str(i)].append(n[0])
                    #datas["all"].append(basedata.zh(self.subject,n[0]))
        return datas

    def saveWb(self,path):
        try:
            self.wb.save(path)
        except PermissionError:
            input("文件{path}无法保存，请关闭文件！".format(path=path))
            self.saveWb(path) 
        except Exception as e:
            print(str(e))

def mainSummary():
    sum=summary()
    sum.mainLoop()
    qkt="缺考考生不参与统计" if basedata.MissingExamination == None else "缺考考生按0分统计"
    if(basedata.ScoreConversion):
        filename=str(root/"output/折合平均分汇总({0}).xlsx".format(qkt))
    else:
        filename=str(root/"output/原始平均分汇总({0}).xlsx".format(qkt))
    sum.saveWb(filename)
    sum=None

basedata.ScoreConversion=False #原始成绩
basedata.MissingExamination = 0 #缺考处理 None：忽略， 0：0分处理
inputdate()
mainSummary()
basedata.MissingExamination = None #缺考处理 None：忽略， 0：0分处理
mainSummary()

basedata.ScoreConversion=True #原始成绩
inputdate()
mainSummary()
basedata.MissingExamination = 0
mainSummary()
# input("已完成汇总，请按Enter退出")
for i in range(4):
    print('\r',f"已完成汇总，{3-i}秒后退出",end='')
    time.sleep(1)
